package com.turing.post.organization.impl;

import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.organization.util.OrganTreeList;
import com.turing.post.organization.util.SysBranchSubord;
import com.turing.post.organization.util.SysBranchSubordDao;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;

/**
 * @author zxp
 * @Jan 3, 2013
 */

@Stateless(mappedName = "organization.impl.SysBranchSubordDaoImpl")
@Remote
@Local
public class SysBranchSubordDaoImpl implements SysBranchSubordDao {
	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@Override
	public void insertSysBranchSubord(SysBranchSubord sysBranchSubord)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, sysBranchSubord);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SysBranchSubord> getSysBranchSubordListBySubBranchNo(
			String subBranchNo) {
		String sql = "select t.* from sysbranchsubord t where t.subbranchno='"
				+ subBranchNo + "' order by t.branchno";
		return _em.createNativeQuery(sql, SysBranchSubord.class)
				.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<OrganTreeList> getAllSysBranchSubordListByBranchNo(
			String branchNo) {
		String sql = "select sbs.subbranchno branchNo,sbs.subbranchsuperno superBranchNo,"
				+ "case when sbs.subbranchtype='01' then (select i.issuecompanyname from IssueCompany i where i.issuecompanyno=sbs.subbranchno) "
				+ "when sbs.subbranchtype='11' then (select ib.IssueBranchName from IssueBranch ib where ib.IssueBranchNo=sbs.subbranchno) "
				+ "when sbs.subbranchtype='12' then (select sb.SaleBranchName from SaleBranch sb where sb.SaleBranchNo=sbs.subbranchno) "
				+ "end as branchName from sysbranchsubord sbs where sbs.branchno='"
				+ branchNo + "' order by sbs.branchno";
		Query query = _em.createNativeQuery(sql, OrganTreeList.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<OrganTreeList> getSysBranchSubordListByBranchNo(String branchNo) {
		String sql = "select sbs.subbranchno branchNo,sbs.subbranchsuperno superBranchNo,"
				+ "case when sbs.subbranchtype='01' then (select i.issuecompanyname from IssueCompany i where i.issuecompanyno=sbs.subbranchno) "
				+ "when sbs.subbranchtype='11' then (select ib.IssueBranchName from IssueBranch ib where ib.IssueBranchNo=sbs.subbranchno) "
				+ "end as branchName from sysbranchsubord sbs where (sbs.subbranchtype='01' or sbs.subbranchtype='11') and sbs.branchno='"
				+ branchNo + "' order by sbs.branchno";
		Query query = _em.createNativeQuery(sql, OrganTreeList.class);
		return query.getResultList();
	}

	/**
	 * 根据机构号查询下属销售机构列表
	 * 
	 * @param branchNo
	 *            机构号
	 * @return List 平台机构隶属关系列表
	 */
	@SuppressWarnings("unchecked")
	public List<SysBranchSubord> getAllSaleBranchListByBranchNo(String branchNo) {
		String sql = "select t.* from sysbranchsubord t where t.subbranchtype = '12' and t.branchno = ?1";
		Query query = _em.createNativeQuery(sql, SysBranchSubord.class);
		query.setParameter(1, branchNo);
		return query.getResultList();
	}

	/**
	 * 根据机构号查询下属发卡商分支机构列表
	 * 
	 * @param branchNo
	 *            机构号
	 * @return List 平台机构隶属关系列表
	 */
	@SuppressWarnings("unchecked")
	public List<SysBranchSubord> getAllIssueBranchListByBranchNo(String branchNo) {
		String sql = "select t.* from sysbranchsubord t where t.subbranchtype = '11' and t.branchno = ?1";
		Query query = _em.createNativeQuery(sql, SysBranchSubord.class);
		query.setParameter(1, branchNo);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<OrganTreeList> getReportSysBranchSubordListByBranchNo(
			String branchNo) {
		String sql = "select sbs.subbranchno branchNo,sbs.subbranchsuperno superBranchNo,i.IssueBranchName branchName from sysbranchsubord sbs,IssueBranch i where sbs.subbranchno=i.IssueBranchNo and sbs.subbranchtype='11' and sbs.branchno=?1 order by sbs.subbranchno";
		Query query = _em.createNativeQuery(sql, OrganTreeList.class);
		query.setParameter(1, branchNo);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public String getReportAllSaleBranchListByBranchNo(String subBranchType,
			String branchNo) {
		String sql = "select t.subbranchno from sysbranchsubord t where (?1 is null or subbranchtype=?1) and t.branchno =?2";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, subBranchType);
		query.setParameter(2, branchNo);
		List<Object> objList = query.getResultList();
		StringBuffer result = new StringBuffer();
		for (Object obj : objList) {
			result.append(obj.toString() + ",");
		}
		if (objList == null || objList.size() < 1)
			return null;
		String res = result.toString();
		res = res.substring(0, res.length() - 1);
		return res;
	}

	@Override
	public void deleteSysBranchSubord(String branchNo)
			throws PCPSApplicationException {
		try {
			String sql1 = "delete from SysBranchSubord where BranchNo='"
					+ branchNo + "' or SubBranchNo='" + branchNo
					+ "' or SubBranchSuperNo='" + branchNo + "'";
			Query query1 = _em.createNativeQuery(sql1);
			query1.executeUpdate();
		} catch (Exception e) {
			throw new PCPSApplicationException(
					PCPSApplicationException.ERRCODE_CATEGORY_DATABASE,
					"delete", "该机构已被引用，禁止删除!", false);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SysBranchSubord> getNextLevelBranchNoList(String branchNo) {
		String sql = "select t.* from sysbranchsubord t where t.subbranchsuperno=?1 and t.branchno=?1 order by subbranchno";
		Query query = _em.createNativeQuery(sql, SysBranchSubord.class);
		query.setParameter(1, branchNo);
		return query.getResultList();
	}
}